﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NewLandOY20
{
    /// <summary>
    /// Access数据库查询语句
    /// </summary>
    static class MyAccessSQLStatement
    {
        // 查询箱号，箱扫描时间， 表号， 表扫描时间
        public static string SelectBoxAndMeter = "SELECT BOX.箱号, BOX.箱扫描时间, Meter.表号, Meter.表扫描时间 FROM BOX INNER JOIN Meter ON BOX.箱号 = Meter.箱号 ";
        // 查询Box表所有信息
        public static string SelectBox = "SELECT * FROM BOX ";
        // 查询订单与箱号
        public static string SelectInvoiceAndBox = "SELECT Invoice.订单号, Invoice.创建时间, BOX.箱号, BOX.箱扫描时间 FROM Invoice INNER JOIN BOX ON Invoice.订单号 = BOX.订单号 ";
        // 查询订单与客户信息
        public static string SelectInvoiceAndUser = "SELECT Invoice.订单号, User.公司, User.收货人姓名, User.收货地址, User.联系电话, Invoice.创建时间 FROM [User] INNER JOIN Invoice ON User.用户号 = Invoice.用户号 ";
        // 查询所有没有订单的箱号
        public static string SelectBoxWithOutInvoice = "SELECT * FROM BOX WHERE [订单号] IS NULL ";
        // 通过表号查找订单信息，如果箱号没有订单号则无结果，使用时在末尾加上要查询的表号和 "';"
        public static string SelectMeterWithInvoice = "SELECT BOX.箱号, Invoice.订单号, User.收货人姓名, User.收货地址, User.联系电话, User.公司, Invoice.创建时间 FROM [User] INNER JOIN (Invoice INNER JOIN (BOX INNER JOIN Meter ON BOX.箱号 = Meter.箱号) ON Invoice.订单号 = BOX.订单号) ON User.用户号 = Invoice.用户号 WHERE (Meter.表号)='";
        // 通过箱扫描时间降序排序
        public static string OrderByBoxScanTimeDesc = "ORDER BY BOX.箱扫描时间 DESC;";
        // 通过订单号降序排序
        public static string OrderByInvoiceDesc = "ORDER BY Invoice.订单号 DESC;";
        // 通过箱表中的订单号降序
        public static string OrderByInvoiceInBoxDes = "ORDER BY Box.箱扫描时间 DESC;";
        // 通过表扫描时间降序排序
        public static string OrderByMeterDesc = "ORDER BY Meter.表扫描时间 DESC;";
        // 查找所有用户信息
        public static string SelectAllUser = "SELECT * FROM [User] ORDER BY 用户号 DESC;";
        // 搜索箱号与相关订单和收货人信息
        public static string SelectBoxAllInfo = "SELECT BOX.箱号, BOX.箱扫描时间, Invoice.订单号, Invoice.创建时间, User.用户号, User.收货人姓名, User.联系电话, User.公司, User.收货地址 FROM [User] INNER JOIN (Invoice INNER JOIN BOX ON Invoice.订单号 = BOX.订单号) ON User.用户号 = Invoice.用户号 WHERE Box.箱号='";
        // 搜索订单号包括的箱号和用户名
        public static string SelectInvoiceBoxUser = "SELECT Invoice.订单号, Invoice.创建时间, User.收货人姓名, BOX.箱号 FROM [User] INNER JOIN (Invoice INNER JOIN BOX ON Invoice.订单号 = BOX.订单号) ON User.用户号 = Invoice.用户号 ";
        // 搜索用户名下的订单信息
        public static string SelectUserAndInvoice = "SELECT User.用户号,User.收货人姓名,User.公司,Invoice.订单号,Invoice.创建时间 FROM [User] INNER JOIN Invoice ON User.用户号 = Invoice.用户号 ";

    }
}
